package edu.mtu.citizenscience.streamapplication.activity;

import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import edu.mtu.citizenscience.streamapplication.activity.main.reports.ModelReport;

import android.content.ContentValues;
import android.content.Context;
import android.content.res.AssetFileDescriptor;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * @author Bryan Becker <bcbecker@mtu.edu>
 * @copyright (c) 2013 MichiganTech <http://www.mtu.edu>
 * 
 * Database class for creating a database instance and calling
 * public methods to find and return information from the stream 
 * database.
 */
public class DBHelper {

	//reference vars for each individual table name in the database
    private static final String DB_TABLE_TREATMENT = "Treatment";
    private static final String DB_TABLE_CULVERT = "Culvert_Desc"; 
    private static final String DB_TABLE_EROSION = "Erosion_Desc";
    private static final String DB_TABLE_LOCATION = "Location";
    private static final String DB_TABLE_ROAD = "Road_Data";
    private static final String DB_TABLE_STREAM = "Stream_Desc";
    private static final String DB_TABLE_PHOTO_NOTES = "Photo_Notes";
    
    //instance of our database helper class
    private DatabaseCreate mDbHelper;
    
    //our created stream database
    private SQLiteDatabase stream_db;

    // reference var for passing the id between activities
    public static final String KEY_ROWID = "_id";
    
	private final Context myContext;

	// reference vars for the name of the database and the version
    private static final String DATABASE_NAME = "stream_db";
    private static final int DATABASE_VERSION = 2;
    
	/**
	 * Internal class for the database containing creation and update methods.
	 */
    private static class DatabaseCreate extends SQLiteOpenHelper {

    	/**
    	 * Database creation handler
    	 * 
    	 * @param context - Context - current state of the application
    	 */
        DatabaseCreate(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

    	/**
    	 * Executes the necessary SQL statements to set up the stream database with tables: Location,
    	 * Erosion_Desc, Road_Data, Stream_Desc, Treatment, Culvert_Desc, and Photo_Notes. The Location
    	 * table contains _id as a primary key for the entire database and all other tables have a foreign 
    	 * key _id linked to _id in the Location table. The database is set to ON DELETE CASCADE for all
    	 * tables other than Location. Therefore, deleting a row with a _id in Location will delete the row
    	 * with that same _id in every other table. 
    	 * 
    	 * @param   db - SQLiteDatabase - Instance of a created SQLite database to call SQL statements on
    	 * @return	void
    	 */
        @Override
        public void onCreate(SQLiteDatabase db) {
        	//SQL statements for creating tables required in the database with their fields
        	db.execSQL("CREATE TABLE Location (_id INTEGER PRIMARY KEY  AUTOINCREMENT UNIQUE NOT NULL , stream_name VARCHAR, county VARCHAR, road_name VARCHAR, crossing_name VARCHAR, township VARCHAR, type_of_crossing VARCHAR, adj_landowners VARCHAR, latitude FLOAT, longitude FLOAT, date_time DATETIME DEFAULT CURRENT_TIME);");
        	db.execSQL("CREATE TABLE Erosion_Desc (_id INTEGER NOT NULL  UNIQUE , erosion_condition_one VARCHAR, erosion_condition_two VARCHAR, erosion_condition_three VARCHAR, erosion_condition_four VARCHAR, erosion_condition_five VARCHAR, erosion_condition_six VARCHAR, erosion_condition_other VARCHAR, erosion_extent VARCHAR, erosion_cause TEXT, FOREIGN KEY(_id) REFERENCES Location (_id) ON DELETE CASCADE);");
        	db.execSQL("CREATE TABLE Road_Data (_id INTEGER UNIQUE NOT NULL, width_at_crossing INTEGER, road_surface VARCHAR, l_approach_length INTEGER, r_approach_length INTEGER, l_approach_slope VARCHAR, r_approach_slope VARCHAR, maintenance VARCHAR, location_low_point VARCHAR, existing_drainage VARCHAR, l_ditch_vegetation VARCHAR, r_ditch_vegetation VARCHAR, width_of_grade INTEGER,  FOREIGN KEY(_id) REFERENCES Location (_id) ON DELETE CASCADE);");
        	db.execSQL("CREATE TABLE Stream_Desc (_id INTEGER UNIQUE NOT NULL, upstream_width INTEGER, upstream_depth INTEGER, upstream_current VARCHAR, downstream_width INTEGER, downstream_depth INTEGER, downstream_current VARCHAR, upstream_substrate VARCHAR, downstream_substrate VARCHAR, adj_wetlands VARCHAR, comments TEXT, FOREIGN KEY(_id) REFERENCES Location (_id) ON DELETE CASCADE);");
        	db.execSQL("CREATE TABLE Treatment (_id INTEGER NOT NULL  UNIQUE , pavement_ft INTEGER, paved_curb_gutter_ft INTEGER, erosion_control_struct INTEGER, sediment_basins INTEGER, replace_culv INTEGER, replace_culv_dia INTEGER, replace_culv_length INTEGER, extend_culv INTEGER, extend_culv_ft INTEGER, diversion_outlets INTEGER, increase_fill INTEGER, place_rock_riprap_ft INTEGER, place_rock_riprap_yd INTEGER, revegetation VARCHAR, bridge VARCHAR, FOREIGN KEY(_id) REFERENCES Location (_id) ON DELETE CASCADE);");
        	db.execSQL("CREATE TABLE Culvert_Desc (_id INTEGER UNIQUE NOT NULL, length INTEGER, diameter INTEGER, material VARCHAR, condition VARCHAR, flow VARCHAR, fish_passage_problem VARCHAR, fill_depth_inlet INTEGER, fill_depth_outlet INTEGER, embankment_inlet VARCHAR, embankment_outlet VARCHAR, FOREIGN KEY(_id) REFERENCES Location (_id) ON DELETE CASCADE);");
        	db.execSQL("CREATE TABLE Photo_Notes (_id INTEGER UNIQUE NOT NULL, photo_one VARCHAR, photo_two VARCHAR, photo_three VARCHAR, notes TEXT, FOREIGN KEY(_id) REFERENCES Location (_id) ON DELETE CASCADE);");

        }

    	/**
    	 * Executes the necessary SQL statements to set up the stream database with tables: Location,
    	 * Erosion_Desc, Road_Data, Stream_Desc, Treatment, Culvert_Desc, and Photo_Notes. The Location
    	 * table contains _id as a primary key for the entire database and all other tables have a foreign 
    	 * key _id linked to _id in the Location table. The database is set to ON DELETE CASCADE for all
    	 * tables other than Location. Therefore, deleting a row with a _id in Location will delete the row
    	 * with that same _id in every other table. 
    	 * 
    	 * @param   db - SQLiteDatabase - Instance of a created SQLite database to call SQL statements on
    	 * @param   oldVersion - int - integer containing the number of the old database version
    	 * @param   newVersion - int - Integer containing the number of our new database version 
    	 * @return	void
    	 */
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        	//print to the log to ensure updating our database was a success
        	Log.w("DBHelper", "Upgrading database from version " + oldVersion + " to "
        			+ newVersion + ", which will destroy all old data");
        	
        	db.execSQL("DROP TABLE IF EXISTS Culvert_Desc;");
        	db.execSQL("DROP TABLE IF EXISTS Erosion_Desc;");
        	db.execSQL("DROP TABLE IF EXISTS Location;");
        	db.execSQL("DROP TABLE IF EXISTS Road_Data;");
        	db.execSQL("DROP TABLE IF EXISTS Stream_Desc;");
        	db.execSQL("DROP TABLE IF EXISTS Treatment;");
        	db.execSQL("DROP TABLE IF EXISTS Photo_Notes;");

        	onCreate(db);
        }
    }
    
    /**
     * Constructor - takes the context to allow the database to be
     * opened/created
     * 
     * @param ctx - Context - the Context within which to work
     */
    public DBHelper(Context ctx) {
        this.myContext = ctx;
    }

    /**
     * Opens the stream database. If it cannot be opened, try to create a new
     * instance of the database. If it cannot be created, throw an exception to
     * signal the failure
     * 
     * @param   void
     * @return this (self reference, allowing this to be chained in an
     *         initialization call)
     * @throws SQLException if the database could be neither opened or created
     */
    public DBHelper open() throws SQLException {
        mDbHelper = new DatabaseCreate(myContext);
        stream_db = mDbHelper.getWritableDatabase();
        return this;
    }
    
   /**
	 * Closes the database.
	 * 
	 * @param   void
	 * @return	void
	 */
    public void close() {
        mDbHelper.close();
    }
    
    // -----------------------------------------------------------------------
    // ADD PUBLIC HELPER METHODS TO ACCESS AND GET CONTENT FROM DATABASE HERE
    // -----------------------------------------------------------------------
	
	/**
	 * Create a row for the Location table given a value for every column 
	 * except date_time and _id. Use the method updateDateTime() to update 
	 * the date_time column for a row in the Location table. The row for
	 * Location is automatically created and provided in the database.
	 * 
	 * @param stream - String - database column
	 * @param county - String - database column
	 * @param road - String - database column
	 * @param crossing - String - database column
	 * @param township - String - database column
	 * @param typeCrossing - String - database column
	 * @param landowners - String - database column
	 * @param latitude - float - database column
	 * @param latitude - float - database column
	 * @return long - the id of the newly created row in the Location table
	 */
    public long createLocation(String stream, String county, String road, String crossing, String township, 
    		String typeCrossing, String landowners, float latitude, float longitude) {    	
    	
        ContentValues initialValues = new ContentValues();
        
        //set the initial values for each field
        //(field name, parameter associated with it)
        initialValues.put("stream_name", stream);
        initialValues.put("county", county);
        initialValues.put("road_name", road);
        initialValues.put("crossing_name", crossing);
        initialValues.put("township", township);
        initialValues.put("type_of_crossing", typeCrossing);
        initialValues.put("adj_landowners", landowners);
        initialValues.put("latitude", latitude);
        initialValues.put("longitude", longitude);
        
        return stream_db.insert(DB_TABLE_LOCATION, null, initialValues);
    }
    
	/**
	 * Update a specific row in the Location table given parameters for every
	 * column and a specific id to find the correct row.
	 * 
	 * @param id - long - database column (primary key)
	 * @param stream - String - database column
	 * @param county - String - database column
	 * @param road - String - database column
	 * @param crossing - String - database column
	 * @param township - String - database column
	 * @param typeCrossing - String - database column
	 * @param landowners - String - database column
	 * @param latitude - float - database column
	 * @param latitude - float - database column
	 * @return boolean - Return true if the database successfully updated a row. False otherwise.
	 */
    public boolean updateLocation(long id, String stream, String county, String road, String crossing,
    		String township, String typeCrossing, String landowners, float latitude, float longitude) {   
    	
        ContentValues args = new ContentValues();
        
        args.put("stream_name", stream);
        args.put("county", county);
        args.put("road_name", road);
        args.put("crossing_name", crossing);
        args.put("township", township);
        args.put("type_of_crossing", typeCrossing);
        args.put("adj_landowners", landowners);
        args.put("latitude", latitude);
        args.put("longitude", longitude);

        return stream_db.update(DB_TABLE_LOCATION, args, KEY_ROWID + "=" + id, null) > 0;
    }
    
	/**
	 * Create a row for the Road_Desc table given a value for every column
	 * including the _id that links it to a row in the Location table.
	 * 
	 * @param id - long - database column (foreign key)
	 * @param crossing - int - database column
	 * @param roadSurface - String - database column
	 * @param leftAppLength - int - database column
	 * @param rightAppLength - int - database column
	 * @param leftAppSlope - String - database column
	 * @param rightAppSlope - String - database column
	 * @param maintenance - String - database column
	 * @param lowPoint - String - database column
	 * @param drainage - String - database column
	 * @param leftDitchVeg - String - database column
	 * @param rightDitchVeg - String - database column
	 * @param widthGrade - int - database column
	 * @return long - the id of the newly created row in the table
	 */
    public long createRoad(long id, int crossing, String roadSurface, int leftAppLength, 
    		int rightAppLength, String leftAppSlope, String rightAppSlope, String maintenance, 
    		String lowPoint, String drainage, String leftDitchVeg, String rightDitchVeg, 
    		int widthGrade) {
    	
        ContentValues initialValues = new ContentValues();
        
        initialValues.put("_id", id);
        initialValues.put("width_at_crossing", crossing);
        initialValues.put("road_surface", roadSurface);
        initialValues.put("l_approach_length", leftAppLength);
        initialValues.put("r_approach_length", rightAppLength);
        initialValues.put("l_approach_slope", leftAppSlope);
        initialValues.put("r_approach_slope", rightAppSlope);
        initialValues.put("maintenance", maintenance);
        initialValues.put("location_low_point", lowPoint);
        initialValues.put("existing_drainage", drainage);
        initialValues.put("l_ditch_vegetation", leftDitchVeg);
        initialValues.put("r_ditch_vegetation", rightDitchVeg);
        initialValues.put("width_of_grade", widthGrade);       

        return stream_db.insert(DB_TABLE_ROAD, null, initialValues);
    }
    
	/**
	 * Update a specific row in the Road table given parameters for every
	 * column and a specific id to find the correct row.
	 * 
	 * @param id - long - database column (foreign key)
	 * @param crossing - int - database column
	 * @param roadSurface - String - database column
	 * @param leftAppLength - int - database column
	 * @param rightAppLength - int - database column
	 * @param leftAppSlope - String - database column
	 * @param rightAppSlope - String - database column
	 * @param maintenance - String - database column
	 * @param lowPoint - String - database column
	 * @param drainage - String - database column
	 * @param leftDitchVeg - String - database column
	 * @param rightDitchVeg - String - database column
	 * @param widthGrade - int - database column
	 * @return boolean - Return true if the database successfully updated a row. False otherwise.
	 */
    public boolean updateRoad(long id, int crossing, String roadSurface, int leftAppLength,
    		int rightAppLength, String leftAppSlope, String rightAppSlope, String maintenance,
    		String lowPoint, String drainage, String leftDitchVeg, String rightDitchVeg,
    		int widthGrade) {
    	
        ContentValues args = new ContentValues();
        
        args.put("_id", id);
        args.put("width_at_crossing", crossing);
        args.put("road_surface", roadSurface);
        args.put("l_approach_length", leftAppLength);
        args.put("r_approach_length", rightAppLength);
        args.put("l_approach_slope", leftAppSlope);
        args.put("r_approach_slope", rightAppSlope);
        args.put("maintenance", maintenance);
        args.put("location_low_point", lowPoint);
        args.put("existing_drainage", drainage);
        args.put("l_ditch_vegetation", leftDitchVeg);
        args.put("r_ditch_vegetation", rightDitchVeg);
        args.put("width_of_grade", widthGrade);       

        return stream_db.update(DB_TABLE_ROAD, args, KEY_ROWID + "=" + id, null) > 0;
    }
    
	/**
	 * Create a row for the Culvert_Desc table given a value for every column
	 * including the _id that links it to a row in the Location table.
	 * 
	 * @param id - long - database column (foreign key)
	 * @param length - int - database column
	 * @param diameter - int - database column
	 * @param material - String - database column
	 * @param condition - String - database column
	 * @param flow - String - database column
	 * @param passageProb - String - database column
	 * @param fillDepthIn - int - database column
	 * @param fillDepthOut - int - database column
	 * @param embankIn - String - database column
	 * @return long - the id of the newly created row in the table
	 */
    public long createCulvert(long id, int length, int diameter, String material, String condition,
    		String flow, String passageProb, int fillDepthIn, int fillDepthOut, String embankIn,
    		String embankOut) {    	
    	
        ContentValues initialValues = new ContentValues();
        
        initialValues.put("_id", id);
        initialValues.put("length", length);
        initialValues.put("diameter", diameter);
        initialValues.put("material", material);
        initialValues.put("condition", condition);
        initialValues.put("flow", flow);
        initialValues.put("fish_passage_problem", passageProb);
        initialValues.put("fill_depth_inlet", fillDepthIn);
        initialValues.put("fill_depth_outlet", fillDepthOut);
        initialValues.put("embankment_inlet", embankIn);
        initialValues.put("embankment_outlet", embankOut);

        return stream_db.insert(DB_TABLE_CULVERT, null, initialValues);
    }
    
	/**
	 * Update a specific row in the Culvert_Desc table given parameters for every
	 * column and a specific id to find the correct row.
	 * 
	 * @param id - long - database column (foreign key)
	 * @param length - int - database column
	 * @param diameter - int - database column
	 * @param material - String - database column
	 * @param condition - String - database column
	 * @param flow - String - database column
	 * @param passageProb - String - database column
	 * @param fillDepthIn - int - database column
	 * @param fillDepthOut - int - database column
	 * @param embankIn - String - database column
	 * @return boolean - Return true if the database successfully updated a row. False otherwise.
	 */
    public boolean updateCulvert(long id, int length, int diameter, String material, String condition, 
    		String flow, String passageProb, int fillDepthIn, int fillDepthOut, String embankIn, 
    		String embankOut) {    	
    	
        ContentValues args = new ContentValues();
        
        args.put("_id", id);
        args.put("length", length);
        args.put("diameter", diameter);
        args.put("material", material);
        args.put("condition", condition);
        args.put("flow", flow);
        args.put("fish_passage_problem", passageProb);
        args.put("fill_depth_inlet", fillDepthIn);
        args.put("fill_depth_outlet", fillDepthOut);
        args.put("embankment_inlet", embankIn);
        args.put("embankment_outlet", embankOut);

        return stream_db.update(DB_TABLE_CULVERT, args, KEY_ROWID + "=" + id, null) > 0;
    }
    
	/**
	 * Create a row for the Stream_Desc table given a value for every column
	 * including the _id that links it to a row in the Location table.
	 * 
	 * @param id - long - database column (foreign key)
	 * @param upstreamWidth - int - database column
	 * @param upstreamDepth - int - database column
	 * @param upstreamCurrent - String - database column
	 * @param downstreamWidth - int - database column
	 * @param downstreamDepth - int - database column
	 * @param downstreamCurrent - String - database column
	 * @param upstreamSubstrate - String - database column
	 * @param downstreamSubstrate - String - database column
	 * @param adjWetlands - String - database column
	 * @param comments - String - database column
	 * @return long - the id of the newly created row in the table
	 */
    public long createStream(long id, int upstreamWidth, int upstreamDepth, String upstreamCurrent,
    		int downstreamWidth, int downstreamDepth, String downstreamCurrent, String upstreamSubstrate,
    		String downstreamSubstrate, String adjWetlands, String comments) {   
    	
        ContentValues initialValues = new ContentValues();
        
        initialValues.put("_id", id);
        initialValues.put("upstream_width", upstreamWidth);
        initialValues.put("upstream_depth", upstreamDepth);
        initialValues.put("upstream_current", upstreamCurrent);
        initialValues.put("downstream_width", downstreamWidth);
        initialValues.put("downstream_depth", downstreamDepth);
        initialValues.put("downstream_current", downstreamCurrent);
        initialValues.put("upstream_substrate", upstreamSubstrate);
        initialValues.put("downstream_substrate", downstreamSubstrate);
        initialValues.put("adj_wetlands", adjWetlands);
        initialValues.put("comments", comments);

        return stream_db.insert(DB_TABLE_STREAM, null, initialValues);
    }
    
	/**
	 * Update a specific row in the Stream_Desc table given parameters for every
	 * column and a specific id to find the correct row.
	 * 
	 * @param id - long - database column (foreign key)
	 * @param upstreamWidth - int - database column
	 * @param upstreamDepth - int - database column
	 * @param upstreamCurrent - String - database column
	 * @param downstreamWidth - int - database column
	 * @param downstreamDepth - int - database column
	 * @param downstreamCurrent - String - database column
	 * @param upstreamSubstrate - String - database column
	 * @param downstreamSubstrate - String - database column
	 * @param adjWetlands - String - database column
	 * @param comments - String - database column
	 * @return boolean - Return true if the database successfully updated a row. False otherwise.
	 */
    public boolean updateStream(long id, int upstreamWidth, int upstreamDepth, String upstreamCurrent,
    		int downstreamWidth, int downstreamDepth, String downstreamCurrent, String upstreamSubstrate,
    		String downstreamSubstrate, String adjWetlands, String comments) {   
    	
        ContentValues args = new ContentValues();
        
        args.put("_id", id);
        args.put("upstream_width", upstreamWidth);
        args.put("upstream_depth", upstreamDepth);
        args.put("upstream_current", upstreamCurrent);
        args.put("downstream_width", downstreamWidth);
        args.put("downstream_depth", downstreamDepth);
        args.put("downstream_current", downstreamCurrent);
        args.put("upstream_substrate", upstreamSubstrate);
        args.put("downstream_substrate", downstreamSubstrate);
        args.put("adj_wetlands", adjWetlands);
        args.put("comments", comments);

        return stream_db.update(DB_TABLE_STREAM, args, KEY_ROWID + "=" + id, null) > 0;
    }
    
	/**
	 * Create a row for the Erosion_Desc table given a value for every column
	 * including the _id that links it to a row in the Location table.
	 * 
	 * @param id - long - database column (foreign key)
	 * @param condOne - String - database column
	 * @param condTwo - String - database column
	 * @param condThree - String - database column
	 * @param condFour - String - database column
	 * @param condFive - String - database column
	 * @param condSix - String - database column
	 * @param other - String - database column
	 * @param extent - String - database column
	 * @param cause - String - database column
	 * @return long - the id of the newly created row in the table
	 */
    public long createErosion(long id, String condOne, String condTwo, String condThree, String condFour,
    		String condFive, String condSix, String other, String extent, String cause) {   
    	
        ContentValues initialValues = new ContentValues();
        
        initialValues.put("_id", id);
        initialValues.put("erosion_condition_one", condOne);
        initialValues.put("erosion_condition_two", condTwo);
        initialValues.put("erosion_condition_three", condThree);
        initialValues.put("erosion_condition_four", condFour);
        initialValues.put("erosion_condition_five", condFive);
        initialValues.put("erosion_condition_six", condSix);
        initialValues.put("erosion_condition_other", other);
        initialValues.put("erosion_extent", extent);
        initialValues.put("erosion_cause", cause);

        return stream_db.insert(DB_TABLE_EROSION, null, initialValues);
    }
    
	/**
	 * Update a specific row in the Erosion_Desc table given parameters for every
	 * column and a specific id to find the correct row.
	 * 
	 * @param id - long - database column (foreign key)
	 * @param condOne - String - database column
	 * @param condTwo - String - database column
	 * @param condThree - String - database column
	 * @param condFour - String - database column
	 * @param condFive - String - database column
	 * @param condSix - String - database column
	 * @param other - String - database column
	 * @param extent - String - database column
	 * @param cause - String - database column
	 * @return boolean - Return true if the database successfully updated a row. False otherwise.
	 */
    public boolean updateErosion(long id, String condOne, String condTwo, String condThree, String condFour,
    		String condFive, String condSix, String other, String extent, String cause) {    	
    	
        ContentValues args = new ContentValues();
        
        args.put("_id", id);
        args.put("erosion_condition_one", condOne);
        args.put("erosion_condition_two", condTwo);
        args.put("erosion_condition_three", condThree);
        args.put("erosion_condition_four", condFour);
        args.put("erosion_condition_five", condFive);
        args.put("erosion_condition_six", condSix);
        args.put("erosion_condition_other", other);
        args.put("erosion_extent", extent);
        args.put("erosion_cause", cause);

        return stream_db.update(DB_TABLE_EROSION, args, KEY_ROWID + "=" + id, null) > 0;
    }
    
	/**
	 * Create a row for the Treatment table given a value for every column
	 * including the _id that links it to a row in the Location table.
	 * 
	 * @param id - long - database column (foreign key)
	 * @param pavementFt - int - database column
	 * @param curbGutterFt - int - database column
	 * @param controlStructures - int - database column
	 * @param sedimentBasins - int - database column
	 * @param replaceCulv - int - database column
	 * @param replaceCulvDia - int - database column
	 * @param replaceCulvlength - int - database column
	 * @param extendCulv - int - database column
	 * @param extendCulvFt - int - database column
	 * @param diversionOutlets - int - database column
	 * @param increaseFill - int - database column
	 * @param ripRapFt - int - database column
	 * @param ripRapYd - int - database column
	 * @param reveg - String - database column
	 * @param bridge - String - database column
	 * @return long - the id of the newly created row in the table
	 */
    public long createTreatment(long id, int pavementFt, int curbGutterFt, int controlStructures, int sedimentBasins,
    		int replaceCulv, int replaceCulvDia, int replaceCulvlength, int extendCulv, int extendCulvFt, 
    		int diversionOutlets, int increaseFill, int ripRapFt, int ripRapYd, String reveg, String bridge) {   
    	
        ContentValues initialValues = new ContentValues();
        
        initialValues.put("_id", id);
        initialValues.put("pavement_ft", pavementFt);
        initialValues.put("paved_curb_gutter_ft", curbGutterFt);
        initialValues.put("erosion_control_struct", controlStructures);
        initialValues.put("sediment_basins", sedimentBasins);
        initialValues.put("replace_culv", replaceCulv);
        initialValues.put("replace_culv_dia", replaceCulvDia);
        initialValues.put("replace_culv_length", replaceCulvlength);
        initialValues.put("extend_culv", extendCulv);
        initialValues.put("extend_culv_ft", extendCulvFt);
        initialValues.put("diversion_outlets", diversionOutlets);
        initialValues.put("increase_fill", increaseFill);
        initialValues.put("place_rock_riprap_ft", ripRapFt);
        initialValues.put("place_rock_riprap_yd", ripRapYd);
        initialValues.put("revegetation", reveg);   
        initialValues.put("bridge", bridge); 
        
        return stream_db.insert(DB_TABLE_TREATMENT, null, initialValues);
    }
    
	/**
	 * Update a specific row in the Treatment table given parameters for every
	 * column and a specific id to find the correct row.
	 * 
	 * @param id - long - database column (foreign key)
	 * @param pavementFt - int - database column
	 * @param curbGutterFt - int - database column
	 * @param controlStructures - int - database column
	 * @param sedimentBasins - int - database column
	 * @param replaceCulv - int - database column
	 * @param replaceCulvDia - int - database column
	 * @param replaceCulvlength - int - database column
	 * @param extendCulv - int - database column
	 * @param extendCulvFt - int - database column
	 * @param diversionOutlets - int - database column
	 * @param increaseFill - int - database column
	 * @param ripRapFt - int - database column
	 * @param ripRapYd - int - database column
	 * @param reveg - String - database column
	 * @param bridge - String - database column
	 * @return boolean - Return true if the database successfully updated a row. False otherwise.
	 */
    public boolean updateTreatment(long id, int pavementFt, int curbGutterFt, int controlStructures, int sedimentBasins,
    		int replaceCulv, int replaceCulvDia, int replaceCulvlength, int extendCulv, int extendCulvFt, 
    		int diversionOutlets, int increaseFill, int ripRapFt, int ripRapYd, String reveg, String bridge) {   
    	
        ContentValues args = new ContentValues();
        
        args.put("_id", id);
        args.put("pavement_ft", pavementFt);
        args.put("paved_curb_gutter_ft", curbGutterFt);
        args.put("erosion_control_struct", controlStructures);
        args.put("sediment_basins", sedimentBasins);
        args.put("replace_culv", replaceCulv);
        args.put("replace_culv_dia", replaceCulvDia);
        args.put("replace_culv_length", replaceCulvlength);
        args.put("extend_culv", extendCulv);
        args.put("extend_culv_ft", extendCulvFt);
        args.put("diversion_outlets", diversionOutlets);
        args.put("increase_fill", increaseFill);
        args.put("place_rock_riprap_ft", ripRapFt);
        args.put("place_rock_riprap_yd", ripRapYd);
        args.put("revegetation", reveg);   
        args.put("bridge", bridge); 
        
        return stream_db.update(DB_TABLE_TREATMENT, args, KEY_ROWID + "=" + id, null) > 0;
    }
    
	/**
	 * Create a row for the Photo_Notes table given a value for every column
	 * including the _id that links it to a row in the Location table.
	 * 
	 * @param id - long - database column (foreign key)
	 * @param photoOne - int - database column
	 * @param photoTwo - int - database column
	 * @param photoThree - int - database column
	 * @param notes - int - database column
	 * @return long - the id of the newly created row in the table
	 */
    public long createPhotoNotes(long id, String photoOne, String photoTwo, String photoThree, String notes) {    	
        ContentValues initialValues = new ContentValues();
        
        initialValues.put("_id", id);
        initialValues.put("photo_one", photoOne);
        initialValues.put("photo_two", photoTwo);
        initialValues.put("photo_three", photoThree);
        initialValues.put("notes", notes);
        
        return stream_db.insert(DB_TABLE_PHOTO_NOTES, null, initialValues);
    }
    
	/**
	 * Update a specific row in the Photo_Notes table given parameters for every
	 * column and a specific id to find the correct row.
	 * 
	 * @param id - long - database column (foreign key)
	 * @param photoOne - int - database column
	 * @param photoTwo - int - database column
	 * @param photoThree - int - database column
	 * @param notes - int - database column
	 * @return boolean - Return true if the database successfully updated a row. False otherwise.
	 */
    public boolean updatePhotoNotes(long id, String photoOne, String photoTwo, String photoThree, String notes) {    	
        ContentValues args = new ContentValues();
        
        args.put("_id", id);
        args.put("photo_one", photoOne);
        args.put("photo_two", photoTwo);
        args.put("photo_three", photoThree);
        args.put("notes", notes);
        
        return stream_db.update(DB_TABLE_PHOTO_NOTES, args, KEY_ROWID + "=" + id, null) > 0;
    }
    
	/**
	 * Update the date_time column in the Location table given a row id.
	 * 
	 * @param id - long - row id for the Location table
	 * @return boolean - Return true if the database successfully updated the row. False otherwise.
	 */
    public boolean updateDateTime(long id) {    
    	
    	//put the date in an easily readable format
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
        Date date = new Date();
        ContentValues args = new ContentValues(); 
        //format the current date
        args.put("date_time", dateFormat.format(date));
        
        return stream_db.update(DB_TABLE_LOCATION, args, KEY_ROWID + "=" + id, null) > 0;
    }
    
	/**
	 * Fetch a row given an id for specific table.
	 * 
	 * @param rowId - long - row id
	 * @param table - String - the table we want the row from
	 * @return Cursor - Return the cursor for the specific row found within the table.
	 */
    public Cursor fetchReport(long rowId, String table) throws SQLException {

        Cursor mCursor = stream_db.rawQuery("SELECT * FROM " + table + " WHERE _id = " + rowId, null);
    	
        //if the cursor isn't null, move it to the first result
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        
        return mCursor;
    }
    
	/**
	 * Check if a row exists within a given table.
	 * 
	 * @param rowId - long - row id
	 * @param table - String - the table we want to check if a row is in
	 * @return boolean - Return true if a row with the given row id exists in the table. False otherwise.
	 */
    public boolean rowExists(long rowId, String table) {
    	//query for the row within a table where the _id is the row id
    	Cursor cursor = stream_db.rawQuery("SELECT 1 FROM " + table + " WHERE _id = " + rowId, null);
    	
    	//if the number of results is greater than 0, we know the row exists
    	boolean exists = (cursor.getCount() > 0);
    	
    	//close the cursor
    	cursor.close();
    	
    	return exists;
    }
    
	/**
	 * This method is used to generate the View/Edit reports list that allows the user to view a list of all
	 * his/her previously created sites. It gathers the row id, name of the stream, and the creation date from all
	 * rows within the Location table. The data in these columns is stored in a ModelReport and stored within an 
	 * array list that is returned and used by ManageReportsActivity to generate a visible list of reports.
	 * 
	 * @param param - void
	 * @return ArrayList<ModelReport> - Return an array list of ModelReports containing information on all rows in the Location table.
	 */
    public ArrayList<ModelReport> fetchList() throws SQLException {
    	
    	ArrayList<ModelReport> reports = new ArrayList();
    	
    	//query the Location table in the database and only gather the necessary fields (row id, stream name, and the creation date)
        Cursor mCursor = stream_db.query(DB_TABLE_LOCATION, new String[] {"_id", "stream_name", "date_time"}, null, null, null, null, null);
    	
        //if the cursor isn't null, move it to the first result
        if (mCursor != null) {
        	//move the cursor to the first result
            mCursor.moveToFirst();
            
            //store our first row in a model report and add it to the array list
            ModelReport r = new ModelReport();
            
            //still need to make a constructor in ModelReport so you don't have to use set methods
			r.setStreamName(mCursor.getString(mCursor.getColumnIndexOrThrow("stream_name")));
			r.setDate(mCursor.getString(mCursor.getColumnIndexOrThrow("date_time")));
			r.setID(mCursor.getLong(mCursor.getColumnIndexOrThrow("_id")));
			reports.add(r);
        }
        
        //while more result rows exist
        while(mCursor.moveToNext()){
        	//store the row information in a model report and add it to the array list
        	ModelReport r = new ModelReport();
			r.setStreamName(mCursor.getString(mCursor.getColumnIndexOrThrow("stream_name")));
			r.setDate(mCursor.getString(mCursor.getColumnIndexOrThrow("date_time")));
			r.setID(mCursor.getLong(mCursor.getColumnIndexOrThrow("_id")));
			reports.add(r);
        }
        
        //close our cursor when done
        mCursor.close();
        
        return reports;
    }
    
	/**
	 * Deletes a report in the database. By deleting a row in the Location table, the database is set
	 * to delete every row in the other tables that corresponds with the Location row id that was deleted.
	 * 
	 * @param id - long - row id we want to delete
	 * @return boolean - If the row was deleted successfully, return true. False otherwise.
	 */
    public boolean deleteReport(long id) 
    {
        return stream_db.delete(DB_TABLE_LOCATION, KEY_ROWID + "='" + id +"'", null) > 0;
    }
    
}